在cmd中创建库
C:\Users\Administrator>mysql -uroot -p -e "create database hh"

在cmd中备份库
C:\Users\Administrator>mysql -uroot -p hh<d:/yc2.sql




2 获得每个班级有多少同学
mysql> select cname,count(cname) as t from stu as s join class as c on s.cid=c.c
id group by c.cid;

3 取得80后最多的是哪个班级
select cname,count(*) as h from stu as s join class as c on s.cid=c.cid 
where birday>19800101 
group by c.cid 
order by h desc 
limit 1;


4求哪个班的女生最多
select cname,count(*) as h from stu as s join class as c on s.cid=c.cid 
where sex=2
group by c.cid
order by h desc
limit 1;


5 找到学生人数最少的班级(2条sql)
select count(*) as h from stu as s join class as c 
on s.cid=c.cid 
group by c.cid 
order by h asc 
limit 1;

select cname  from stu as s join class as c 
on s.cid=c.cid 
group by c.cid 
having count(*)=1;

6 男同学最喜欢上哪个班
select cname,count(*) as h from stu as s join class as c on s.cid=c.cid 
where sex=1
group by c.cid
order by h desc
limit 1;

8 求出哪个学生没有班级
select * from stu as s left join class as c on c.cid=s.cid where c.cid is null;

9 求出哪个班级没有学生
select * from stu as s right join class as c on c.cid=s.cid where s.cid is null;


10 得到和向军在一个班的同学(自链接)
//select cid from stu where sname="向军"//这是向军在班级cid
方法1：select sname from stu where cid in (select cid from stu where sname="向军");
方法2：select s2.sname from stu as s1 join stu as s2 on s1.cid=s2.cid where s1.sname="向军";


作业：
1 检索出学生的课程id
select sname,lid from stu as s join stu_lesson_relation as sl 
on s.sid=sl.sid;

2 检索出小玉都学习的课名
select lname from stu as s join stu_lesson_relation as sl join lesson as l
on s.sid=sl.sid and sl.lid=l.lid
where sname="小玉";

3 检索出和小玉都学一样课程的学生(2条sql)
select lid from stu as s join stu_lesson_relation as sl 
on s.sid=sl.sid
where sname="小玉";

select distinct sname from stu as s join stu_lesson_relation as sl 
on s.sid=sl.sid
where sl.lid in(1,3,2);

4 检索出每个学生学习的课程
select sname,lname from stu as s join stu_lesson_relation as sl join lesson as l
on s.sid=sl.sid and sl.lid=l.lid order by s.sid;

5 检索出每个课程学员数量 
select lname,count(*) as num from stu_lesson_relation as sl join lesson as l
on sl.lid=l.lid
group by l.lid;

6 检索出女生学的最多的课程
select lname from stu as s join stu_lesson_relation as sl join lesson as l
on s.sid=sl.sid and sl.lid=l.lid
where s.sex=2
group by l.lid
order by count(*) desc 
limit 1;









